﻿//Copyright (C) <2013>  <jonathan cleeve norton> All Rights Reserved 
//Contact jon.norton@fin-plus.co.uk website <http://www.fin-plus.co.uk/>
using System;
using System.Collections.Concurrent;
using System.Data.SqlServerCe;
using System.Reflection;
using Messaging2;
using FinPlusCommon;
using u = FinPlusUtility.Utility;
using log = Logger.Logger;

namespace FinPlusDBConn
{
    public sealed class FinPlusConn : IDisposable, IFinPlusDBConn
    {
        public event EventHandler QueryUpdate;

        private const string FunctionMap = "FunctionMap";
        private ConcurrentBag<IFinPlusQuery> _finPlusQuerys = new ConcurrentBag<IFinPlusQuery>();
        private ConcurrentDictionary<string, string> _funcGroupMap;
        private ITransport _tran;
        private u.Env _env;
        private readonly string _user, _connString;
        private SqlCeConnection _conn;
        private readonly u.Config _config;

        //construct
        public FinPlusConn(string connString = null)
        {
            _connString = connString;
            _user = Environment.UserName;

            var p = u.GetConfigPath(Assembly.GetExecutingAssembly());
            _config = u.ConfigXml(p, out _env);
            log.Initialise(p, _env.ToString());

            Initialise();
        }

        public IFinPlusQuery Query(QueryType queryType, string group, DateTime date, string where, ConType conType = ConType.None, string displayFields = "", string displayOrder = "")
        {
            switch (queryType)
            {
                case QueryType.Select: return Select(group, where, conType, displayFields, displayOrder);
                case QueryType.SelectAsOf: return SelectAsOf(group, date, where, conType, displayFields, displayOrder);
                case QueryType.SelectFrom: return SelectFrom(group, date, where, conType, displayFields, displayOrder);
                default: return null;
            }
        }

        //public 
        public string Save(IFunction func)
        {
            try
            {
                if (func.Name == FunctionMap) 
                    return FuncMapSave(func);
                return FuncSave(func);
            }
            catch(Exception e)
            {
                return e.ToString();
            }
        }

        public string Close(IFunction func) 
        {
            try
            {
                if (func.Name == FunctionMap) 
                    return "Cant perform action on MapFunction";
                return FuncClose(func);
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        public IFinPlusQuery SaveFunc(string group, string where, ConType conType = ConType.None, string displayFields = "", string displayOrder = "")
        {
            var q = new Query(_conn, _tran, QueryType.SaveFunc, group, DateTime.MinValue, DateTime.MinValue, where, conType, displayFields, displayOrder);
            _finPlusQuerys.Add(q); 
  
            return q; 
        }

        public IFinPlusQuery Select(string group, string where, ConType conType = ConType.None, string displayFields = "", string displayOrder = "")
        {
            var q = new Query(_conn, _tran, QueryType.Select, group, DateTime.MinValue, DateTime.MinValue, where, conType, displayFields, displayOrder);
            if (q.ConType != ConType.None) 
                _finPlusQuerys.Add(q); 
            q.Execute(); 

            return q; 
        }

        public IFinPlusQuery SelectAsOf(string group, DateTime asOf, string where, ConType conType = ConType.None, string displayFields = "", string displayOrder = "")
        {
            var q = new Query(_conn, _tran, QueryType.Select, group, asOf, DateTime.MinValue, where, conType, displayFields, displayOrder);
            q.Execute(); 

            return q;
        }

        public IFinPlusQuery SelectFrom(string group, DateTime from, string where, ConType conType = ConType.None, string displayFields = "", string displayOrder = "")
        {
            var q = new Query(_conn, _tran, QueryType.Select, group, DateTime.MinValue, from, where, conType, displayFields, displayOrder);
            if (q.ConType != ConType.None) 
                _finPlusQuerys.Add(q); 
            q.Execute(); 
            
            return q;
        }

        public IFinPlusQuery SelectFuncById(string group, int Id)
        {
            var q = Select(group, @"Name = 'Id' AND Value = '" + Id.ToString() + @"'", ConType.None);
            if (q.ConType != ConType.None)
                _finPlusQuerys.Add(q); 
            q.Execute();

            return q;
        }

        public void Dispose()
        {
            if (_conn != null)
            {
                _conn.Close();
                _conn.Dispose();
                _conn = null;
            }
        }

        public void DisposeQuery(IFinPlusQuery finPlusQuery)
        {
            _finPlusQuerys.TryTake(out finPlusQuery);
        }

        //private
        private void Initialise()
        {
            if (_tran == null)
            {
                _tran = new Transport(_config["messagingsettings"]["address"], int.Parse(_config["messagingsettings"]["port"]));
                _tran.Listener.MessageRecieved += Query_Update;
            }

            _conn = new SqlCeConnection();
            if (!string.IsNullOrEmpty(_connString))
                _conn.ConnectionString = _connString;
            else
                _conn.ConnectionString = string.Format(@"Data Source = '{0}';Password='{1}';", _config["databasesettings"]["datasource"], _config["databasesettings"]["password"]);

            _conn.Open();

            if (_funcGroupMap == null)
                _funcGroupMap = FuncGroupMap();
        }

        private string FuncSave(IFunction func)
        {
            try
            {
                if (!_funcGroupMap.ContainsKey(func.Name)) return func.Error = @"Table not setup use =FunctionMap(?, '" + func.Name + "')";

                var q = new Query(func, _conn, _tran, QueryType.SaveFunc, _funcGroupMap[func.Name], _user);
                q.Execute();
                return func.Id.ToString();
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        private string FuncClose(IFunction func)
        {
            try
            {
                if( func.Id < 0 ) return "You cant delete item not saved";

                var q = new Query(func, _conn, _tran, QueryType.CloseFunc, _funcGroupMap[func.Name], _user);
                q.Execute();

                return "Deleted " + func.Id.ToString();
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        private string FuncMapSave(IFunction func)
        {
            var funcName = func["Name"].ToString();
            if (!_funcGroupMap.ContainsKey(funcName))
            {
                var funcGroup = func["Group"].ToString();
                FuncGroupCreateTables(funcGroup);
                _funcGroupMap[funcName] = funcGroup;
                return FuncSave(func);
            }

            return func.Error = "Already Mapped";
        }

        private ConcurrentDictionary<string, string> FuncGroupMap()
        {
            var map = new ConcurrentDictionary<string, string>();
            map[FunctionMap] = FunctionMap;

            FuncGroupCreateTables(FunctionMap);
            var q = Select(FunctionMap, string.Empty, ConType.None);

            q.Execute();
            foreach (var f in q.Functions.Values)
                map.TryAdd(f["Name"].ToString(), f["Group"].ToString());

            return map;
        }

        private void FuncGroupCreateTables(string group)
        {
            try
            {
                if (group == string.Empty || group.Equals("FunctionMap")) return;


                //TODO put both creation tables together so no incosistency if one fails
                var sql1 = @"CREATE TABLE " + group + @"Ids" +
                            @"(
                                    [Id] int primary key IDENTITY(1,1) NOT NULL,
                                    [TimeStamp] datetime NOT NULL,
                                    [User] nvarchar(50) NOT NULL,
                                    [Closed] datetime,
                                    [ClosedBy] nvarchar(50), 
                                    [LastChanged] datetime NOT NULL
                            )";

                lock (_conn) using (var cmd = new SqlCeCommand(sql1, _conn)) cmd.ExecuteNonQuery();

                var sql2 = @"CREATE TABLE " + group + @"Items" +
                            @"(
                                    [Id] int NOT NULL,
                                    [ParamPos] int NOT NULL,
                                    [Name] nvarchar(50) NOT NULL,
                                    [Value] nvarchar(200) NOT NULL,
                                    [Type] nvarchar(50) NOT NULL,
                                    [TimeStamp] datetime NOT NULL,
                                    [User] nvarchar(50) NOT NULL,
                                    CONSTRAINT pk_" + group + @"Items PRIMARY KEY ([Id], [Name], [TimeStamp])
                            )";
                //

                lock (_conn) using (var cmd = new SqlCeCommand(sql2, _conn)) cmd.ExecuteNonQuery();


            }
            catch 
            {
                  //utilities need to add logging
            }

        }

        private void FinPlusQuerysUpdate(IMessage msg)
        {
            var func = new Function(msg);

            if (!_funcGroupMap.ContainsKey(func.Name)) return;

            var group = _funcGroupMap[func.Name];

            foreach (var q in _finPlusQuerys)
                if (q.Group == group && q.Update(func) && QueryUpdate != null)
                    QueryUpdate.Invoke(this, new FinPlusDBConnEventArgs(q, FinPlusDBConnEventType.UpdateQuery)); 
        }

        //events
        private void Query_Update(object s, EventArgs a)
        {
            var msgEvent = (MessageEventArgs)a;
            FinPlusQuerysUpdate(msgEvent.Message);
        }
    }

    internal enum FinPlusDBConnEventType { UpdateQuery, CloseQuery } 

    internal class FinPlusDBConnEventArgs : EventArgs
    {
        public IFinPlusQuery FinPlusQuery { get; private set; }
        public FinPlusDBConnEventType Type { get; private set; }

        public FinPlusDBConnEventArgs(IFinPlusQuery finPlusQuery, FinPlusDBConnEventType type) 
        {
            FinPlusQuery = finPlusQuery;
            Type = type;
        } 
    } 
}
